Working with Strings

Visual Basic for Applications includes many powerful string functions, and it's sometimes difficult at first glance to determine which one meets your requirements. In this section, I briefly describe all the string functions at your disposal, offer some tips for selecting the most suitable one in some typical situations, and also provide some useful string functions that you can reuse in your applications.

The basic string operator & performs a string concatenation. The result is a string consisting of all the characters of the first string followed by all the characters of the second string:

Many programmers with roots in QuickBasic still use the + operator for performing string concatenation. This is a dangerous practice that impacts code readability and might introduce unexpected behaviors when either operand isn't a string.

The next bunch of popular string functions, shown below, includes Left$, Right$, and Mid$, which extract a substring from the beginning, the end, or the middle of the source string.

Text = "123456789"
Print Left$(text, 3)         ' Displays "123"
Print Right$(text, 2)        ' Displays "89"
Print Mid$(text, 3, 4)       ' Displays "3456"

TIP
The VBA documentation consistently omits the trailing $ character in all string functions and invites you to use the new $-less functions. Don't do it! A $-less function returns a Variant that contains the string result, which means in most cases the Variant must be reconverted to a string before it can be reused in expressions or assigned to a String variable. This is a time-consuming process that gives you nothing in return. Informal benchmarks show that, for example, the Left$ function is up to twice as fast as its $-less counterpart. A similar reasoning applies to other functions that exist in both forms, including LCase, UCase, LTrim, RTrim, Trim, Chr, Format, Space, and String.

Mid$ can also work as a command in that it lets you modify one or more characters inside a string:

The Len function returns the current length of a string. It's often used to test whether a string contains any characters:

Print Len("12345")          ' Displays "5"
If Len(Text) = 0 Then ...   ' Faster than comparison with an empty string.

To discard unwanted trailing or leading blanks, you can use the LTrim$, RTrim$, and Trim$ functions:

Text = "  abcde  "
Print LTrim$(Text)           ' Displays "abcde  "
Print RTrim$(Text)           ' Displays "  abcde"
Print Trim$(Text)            ' Displays "abcde"

These functions are especially useful with fixed-length strings that are filled with extra spaces to account for their expected length. You can trim those extra spaces using the RTrim$ function:

Dim Text As String * 10
Text = "abcde"               ' Text now contains "abcde     ".
Print Trim$(Text)            ' Displays "abcde"

CAUTION
When a fixed-length string is declared but hasn't been used yet, it contains Null characters, not spaces. This means that the RTrim$ function can't trim such a string:
Dim Text As String * 10
Print Len(Trim$(Text))       ' Displays "10", no trimming has occurred.
You can avoid this problem by simply assigning an empty string to all the fixed-length strings in your application soon after their declaration and before using them.

The Asc function returns the character code of the first letter in a string. Functionally, it's similar to extracting the first character using the Left$ function, but Asc is considerably faster:

If Asc(Text) = 32 Then        ' Test whether the fist char is a space.
If Left$(Text, 1) = " " Then  ' Same effect, but 2 to 3 times slower

When you're using the Asc function, you should ensure that the string isn't empty because in that case the function raises an error. In a sense, Chr$ is the opposite of Asc in that it transforms a numeric code into the corresponding character:

The Space$ and String$ functions are very similar. The former returns a string of spaces of the length you want, and the latter returns a string that consists of the character specified in the second parameter repeated as many times as you indicated in the first parameter:

Print Space$(5)               ' Displays "     " (five spaces)
Print String$(5, " ")         ' Same effect
Print String$(5, 32)          ' Same effect, using the char code
Print String$(50, ".")        ' A row of 50 dots

Finally the StrComp function lets you compare strings in a case-insensitive fashion and returns -1, 0, or 1 if the first argument is less than, equal to, or greater than the second argument. The third argument specifies whether the comparison should be performed in a case-insensitive way:

Select Case StrComp(first, second, vbTextCompare)
    Case 0
        ' first = second   (e.g. "VISUAL BASIC" vs. "Visual Basic")
    Case -1
        ' first < second   (e.g. "C++" vs. "Visual Basic")
    Case 1
        ' first > second   (e.g. "Visual Basic" vs. "Delphi")
End Select

The StrComp function is sometimes convenient even for case-sensitive comparisons because you don't need two separate tests to decide whether a string is less than, equal to, or greater than another one.

The most frequently used functions for converting strings are UCase$ and LCase$, which transform their arguments to uppercase and lowercase, respectively:

Text = "New York, USA"
Print UCase$(Text)                 ' "NEW YORK, USA"
Print LCase$(Text)                 ' "new york, usa"

The StrConv function encompasses the functionality of the first two and adds more capabilities. You can use it to convert to uppercase, lowercase, and propercase (where the first letter of each word is uppercase, and all the others are lowercase):

Print StrConv(Text, vbUpperCase)   ' "NEW YORK, USA"
Print StrConv(Text, vbLowerCase)   ' "new york, usa"
Print StrConv(Text, vbProperCase)  ' "New York, Usa"

(Valid word separators are spaces, Null characters, carriage returns, and line feeds.) The function can also perform ANSI-to-Unicode conversion and back, using the vbUnicode and vbFromUnicode symbolic constants. You'll rarely use these functions in your regular Visual Basic applications.

The Val function converts a string into its decimal representation. (See also the section "Converting Among Different Numeric Bases," earlier in this chapter). Visual Basic also includes functions that can convert from a string to a numeric value, such as CInt, CLng, CSng, CDbl, CCur, and CDate. The main difference between them and the Val function is that they're locale aware. For example, they correctly recognize the comma as the decimal separator in countries where this is the case and ignore any thousand separator characters. Conversely, the Val function recognizes only the decimal point and stops parsing its argument when it finds any invalid characters (including a currency symbol or a comma used for grouping thousand digits).

The Str$ function converts a number into its string representation. The main difference between Str$ and CStr is that the former adds a leading space if the argument is positive, whereas the latter does not.

The InStr function searches for a substring in another string, either in case-sensitive or case-insensitive mode. You can't omit the starting index if you want to pass the argument that specifies which kind of search you want to perform:

Print InStr("abcde ABCDE", "ABC")     ' Displays "7" (case sensitive)
Print InStr(8, "abcde ABCDE", "ABC")  ' Displays "0" (start index > 1)
Print InStr(1, "abcde ABCDE", "ABC", vbTextCompare)
                                      ' Displays "1" (case insensitive)

The InStr function is very handy for building other powerful string functions that are missing in the VBA language. For example, this is a function that searches for the first occurrence of a character among those included in a search table. It's useful for extracting words that can be delimited by many different punctuation characters:

Function InstrTbl(source As String, searchTable As String, _
    Optional start As Long = 1, _
    Optional Compare As VbCompareMethod = vbBinaryCompare) As Long
    Dim i As Long
    For i = start To Len(source)
        If InStr(1, searchTable, Mid$(source, i, 1), Compare) Then
            InstrTbl = i
            Exit For
        End If
    Next
End Function

Visual Basic 6 lets you perform backward searches, using the new InStrRev function. Its syntax is similar to the original InStr function, but the order of its arguments is different:

Here are a few examples. Note that if you omit the start argument, the search starts at the end of the string:

Print InStrRev("abcde ABCDE", "abc")    ' Displays "1" (case sensitive)
Print InStrRev("abcde ABCDE", "abc", ,vbTextCompare )  
                                        ' Displays "7" (case insensitive)
Print InStrRev("abcde ABCDE", "ABC", 4, vbTextCompare )
                            ' Displays "1" (case insensitive, start<>0)

Visual Basic also includes a handy string operator, the Like operator, which is often a life saver when you're parsing a string and performing complex searches. The syntax of this operator is the following:

where string is the string being parsed and pattern is a string made up of special characters that define the search condition. The most frequently used special characters are ? (any single character), * (zero or more characters), and # (any single digit). Here are a few examples:

' The Like operator is affected by the current Option Compare setting.
Option Compare Text                 ' Enforce case-insensitive comparisons.
' Check that a string consists of "AB" followed by three digits.
If value Like "AB###" Then ...      ' e.g. "AB123" or "ab987"
' Check that a string starts with "ABC" and ends with "XYZ".
If value Like "ABC*XYZ" Then ...    ' e.g. "ABCDEFGHI-VWXYZ"
' Check that starts with "1", ends with "X", and includes 5 chars.
If value Like "1???X" Then ...      ' e.g. "1234X" or "1uvwx"

You can also specify which characters you want to include (or exclude) in the search by inserting a list enclosed in square brackets:

' One of the letters "A","B","C" followed by three digits
If value Like "[A-C]###" Then ...           ' e.g. "A123" or "c456"
' Three letters, the first one must be a vowel
If value Like "[AEIOU][A-Z][A-Z]" Then...  ' e.g. "IVB" or "OOP"
' At least three characters, the first one can't be a digit.
' Note: a leading "!" symbol excludes a range.
If value Like "[!0-9]??*" Then ...  ' e.g. "K12BC" or "ABHIL"

Visual Basic 6 introduces the new Replace function, which quickly finds and replaces substrings. The syntax of this function isn't straightforward because the function includes several optional arguments:

The simplest form searches substrings in case-sensitive mode and replaces all occurrences:

By acting on the other arguments, you can start your search from a different position, limit the number of substitutions, and perform a case-insensitive search. Note that a value for start greater than 1 actually trims the source argument before starting the search:

Print Replace("abc ABC abc", "ab", "123", 5, 1)                ' "ABC 123c"
Print Replace("abc ABC abc", "ab", "123", 5, 1, vbTextCompare) ' "123C abc"

You can also use the Replace function in a somewhat unorthodox way to count the number of occurrences of a substring inside another string:

Function InstrCount(Source As String, Search As String) As Long
    ' You get the number of substrings by subtracting the length of the 
    ' original string from the length of the string that you obtain by 
    ' replacing the substring with another string that is one char longer.
    InstrCount = Len(Replace(Source, Search, Search & "*")) - Len(Source)
End Function

The new StrReverse function quickly reverses the order of characters in a string. This function is rarely useful in itself, but it adds value to other string-processing functions:

' Replace only the LAST occurrence of a substring.
Function ReplaceLast(Source As String, Search As String, _
    ReplaceStr As String) As String
        ReplaceLast = StrReverse(Replace(StrReverse(Source), _
            StrReverse(Search), StrReverse(ReplaceStr), , 1))
End Function

You can use the new Split function to find all the delimited items in a string. Its syntax is the following:

where delimiter is the character used to delimit individual items. You can pass a positive value for the limit argument if you don't want more items than a given value, and you can pass the vbTextCompare value to the last argument to perform case-insensitive searches. Since the default delimiter character is the space, you can easily extract all the words in a sentence using this code:

Dim words() As String
words() = Split("Microsoft Visual Basic 6")
' words() is now a zero-based array with four elements.

The Join function is complementary to the Split function in that it accepts an array of strings and one delimiter character and rebuilds the original string:

' Continuing the preceding example ...
' The delimiter argument is optional here, because it defaults to " ".
Print Join(words, " ")       ' Displays "Microsoft Visual Basic 6"

Note that the delimiter argument in both the Split and Join functions can be longer than just one character.

Another welcome addition to the VBA language is the Filter function, which quickly scans an array searching for a substring and returns another array that contains only the items that include (or don't include) the searched substring. The syntax for the Filter function is the following:

If the Include argument is True or omitted, the result array contains all the items in source that contain the search substring; if it's False, the result array contains only the items that don't contain it. As usual, the CompareMethod argument specifies whether the search is case sensitive:

ReDim s(2) As String
s(0) = "First": s(1) = "Second": s(2) = "Third"
Dim res() As String
res = Filter(s, "i", True, vbTextCompare)
' Print the result array  ("First" and "Third").
For i = 0 To UBound(res): Print res(i): Next

If no items in the source array meet the search requirements, the Filter function delivers a special array that returns _1 when passed to the UBound function.

You can also use the Format function to format strings. In this case, you can specify only a custom format (no named formats are available for string data) and you have a limited choice of special characters, but you can get a lot of flexibility anyway. You can specify two sections, one for non-empty string values and one for empty string values as shown below.

' By default, placeholders are filled from right to left.
' "@" stands for a character or a space, "&" is a character or nothing.
Print Format("abcde", "@@@@@@@")                          ' "  abcde"
' You can exploit this feature to right align numbers in reports.
Print Format(Format(1234.567, "Currency"), "@@@@@@@@@@@") ' "  $1,234.57"
' "!" forces left to right fill of placeholders.
Print Format("abcde", "!@@@@@@@")                         ' "abcde  "
' ">" forces to uppercase, "<" forces to lowercase.
Print Format("abcde", ">& & & & &")                       ' "A B C D E"
' This is a good way to format phone numbers or credit-card numbers.
Print Format("6152127865", "&&&-&&&-&&&&")                ' "615-212-7865"
' Use a second section to format empty strings.
' "\" is the escape character.
Print Format("", "!@@@@@@@;\n\o\n\e")                     ' "none"

Working with Strings

Basic String Operators and Functions

Conversion Functions

Find and Replace Substrings

Format Options for Strings